package com.example.demo.service;

import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * 用户查询服务类：支持多条件组合查询，并打印执行时间
 */
public class UserQueryService {

    private final String url;
    private final String username;
    private final String password;

    public UserQueryService(String url, String username, String password) {
        this.url = url;
        this.username = username;
        this.password = password;
    }

    /**
     * 多条件查询用户
     *
     * @param status             状态（1=启用，0=禁用），null 表示不限
     * @param usernameLike       用户名模糊匹配，null 或空字符串表示不限
     * @param createTimeStart    创建时间 >=，null 表示不限
     * @param createTimeEnd      创建时间 <=，null 表示不限
     * @return 用户列表
     */
    public List<User> queryUsers(
            Integer status,
            String usernameLike,
            String fullNameLike,        // 新增：姓名模糊查询
            LocalDateTime createTimeStart,
            LocalDateTime createTimeEnd
    ) {
        List<User> users = new ArrayList<>();
        List<Object> params = new ArrayList<>();

        // 动态构建 SQL
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT id, username, full_name, email, phone, status, create_time ")
                .append("FROM sys_user WHERE 1=1");

        if (status != null) {
            sql.append(" AND status = ?");
            params.add(status);
        }
        if (usernameLike != null && !usernameLike.trim().isEmpty()) {
            sql.append(" AND username LIKE ?");
            params.add("%" + usernameLike + "%");
        }
        // ✅ 新增：姓名模糊查询
        if (fullNameLike != null && !fullNameLike.trim().isEmpty()) {
            sql.append(" AND full_name LIKE ?");
            params.add("%" + fullNameLike + "%");
        }
        if (createTimeStart != null) {
            sql.append(" AND create_time >= ?");
            params.add(createTimeStart);
        }
        if (createTimeEnd != null) {
            sql.append(" AND create_time <= ?");
            params.add(createTimeEnd);
        }
        sql.append(" ORDER BY create_time DESC");

        long startNano = System.nanoTime();
        try (Connection conn = DriverManager.getConnection(url, this.username, this.password);
             PreparedStatement pstmt = conn.prepareStatement(sql.toString())) {

            // 设置参数
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(i + 1, params.get(i));
            }

            System.out.printf("[SQL] 执行查询: %s%n", sql.toString());
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    User user = new User();
                    user.setId(rs.getLong("id"));
                    user.setUsername(rs.getString("username"));
                    user.setFullName(rs.getString("full_name"));
                    user.setEmail(rs.getString("email"));
                    user.setPhone(rs.getString("phone"));
                    user.setStatus(rs.getInt("status"));
                    user.setCreateTime(rs.getTimestamp("create_time").toLocalDateTime());
                    users.add(user);
                }
            }

        } catch (SQLException e) {
            System.err.println("❌ 数据库查询失败: " + e.getMessage());
            e.printStackTrace();
            return users;
        }

        long durationMs = (System.nanoTime() - startNano) / 1_000_000;
        System.out.printf("✅ 查询完成，匹配 %d 条记录，耗时: %d ms%n", users.size(), durationMs);

        return users;
    }
    public String exportUsersToCsv(
            Integer status,
            String usernameLike,
            String fullNameLike,
            LocalDateTime createTimeStart,
            LocalDateTime createTimeEnd,
            String outputPath
    ) {
        long totalStartNano = System.nanoTime();

        // ✅ 1. 执行查询
        List<User> users = queryUsers(status, usernameLike, fullNameLike, createTimeStart, createTimeEnd);
        if (users.isEmpty()) {
            System.out.println("⚠️  查询结果为空，跳过导出。");
            return null;
        }

        // ✅ 2. 确定输出路径
        if (outputPath == null || outputPath.trim().isEmpty()) {
            String timestamp = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMdd_HHmmss"));
            outputPath = "users_export_" + timestamp + ".csv";
        }

        Path outputDir = Paths.get(outputPath).getParent();
        if (outputDir != null && !Files.exists(outputDir)) {
            try {
                Files.createDirectories(outputDir);
            } catch (IOException e) {
                System.err.println("❌ 创建目录失败: " + outputDir);
                e.printStackTrace();
                return null;
            }
        }

        // ✅ 3. 写入 CSV 文件
        try (BufferedWriter writer = new BufferedWriter(new FileWriter(outputPath))) {
            // 写表头
            writer.write("ID,用户名,姓名,邮箱,电话,状态,创建时间");
            writer.newLine();

            // 写数据
            for (User user : users) {
                writer.write(String.format("%d,%s,%s,%s,%s,%d,%s",
                        user.getId(),
                        escapeCsv(user.getUsername()),
                        escapeCsv(user.getFullName()),
                        escapeCsv(user.getEmail()),
                        escapeCsv(user.getPhone()),
                        user.getStatus(),
                        user.getCreateTime().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"))
                ));
                writer.newLine();
            }

            long durationMs = (System.nanoTime() - totalStartNano) / 1_000_000;
            System.out.printf("✅ 数据已导出到: %s （共 %d 条，总耗时: %d ms）%n", outputPath, users.size(), durationMs);
            return outputPath;

        } catch (IOException e) {
            System.err.println("❌ 导出文件失败: " + e.getMessage());
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 简单的 CSV 转义（处理逗号、换行、引号）
     */
    private String escapeCsv(String value) {
        if (value == null) return "";
        if (value.contains(",") || value.contains("\"") || value.contains("\n")) {
            return "\"" + value.replace("\"", "\"\"") + "\"";
        }
        return value;
    }
}